"""sql 查询文件"""

import logging

from database.connection import Link_database
# from ydsp.database.connection import Link_database

logger = logging.getLogger(__name__)


def asset_alloca(user_id):
    """资产查询"""
    con, obj = Link_database()

    sql = """select c.*,d.department from depar as d inner join (select a.id,a.assets_name,a.department,b.name,a.state,a.note from assets as a inner join user as b on a.user_name=b.id where a.user_name=%d) as c on d.id=c.department""" % user_id
    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


def sel_entry(id):
    """用户查询"""
    con, obj = Link_database()

    sql = """select a.*,b.department from user as a left join depar as b on a.department=b.id where a.id=%d""" % id

    obj.execute(sql)
    q = obj.fetchone()

    obj.close()
    con.close()

    return q


def all_asset_alloca(page):
    """总资产查询"""
    con, obj = Link_database()

    sql = """select assets_name,count(id) from assets group by assets_name"""
    obj.execute(sql)
    q = obj.fetchall()

    sql_1 = """select assets_name,count(id) from assets where user_name=0 group by assets_name"""
    obj.execute(sql_1)
    r = obj.fetchall()

    sql_2 = """select assets_name,count(id) from assets where state='完好' group by assets_name"""
    obj.execute(sql_2)
    y = obj.fetchall()

    sql_3 = """select c.*,d.department from depar as d right join (select a.id,a.assets_name,a.department,b.name,a.state,a.note from assets as a left join user as b on a.user_name=b.id order by id limit %d,20) as c on d.id=c.department""" % page
    obj.execute(sql_3)
    sum = obj.fetchall()

    query_list_1 = []
    for i in q:
        for a in r:
            if i[0] == a[0]:
                l = [i[0], i[1], a[1]]
                query_list_1.append(l)
    query_list = []
    for i in query_list_1:
        for a in y:
            if i[0] == a[0]:
                l = [i[0], i[1], i[2], a[1]]
                query_list.append(l)

    obj.close()
    con.close()

    return query_list, sum


def asset_name():
    """查看资产名称"""
    con, obj = Link_database()

    sql = """select assets_name from assets group by assets_name"""
    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


def asset_serial(asset_name):
    """名称下所有编号查询"""
    con, obj = Link_database()

    sql = """select id from assets where assets_name='%s' and user_name=0""" % asset_name
    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


def asset_user(department):
    """部门下员工"""
    con, obj = Link_database()

    sql = """select id,name from user where department=%d""" % department
    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q